﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Xml.Linq;
using TeacherLeftHand.Model;
using log4net;
using TeacherLeftHand.TLH_DB_DataSetTableAdapters;

namespace TeacherLeftHand.DataAccess
{
    class TeachingMethodAccessor
    {
        #region Constants

        // XML document FUll File Path (../bin/debug/...xml)
        //String XmlFile = AppDomain.CurrentDomain.BaseDirectory + "_ki.xml";

        #endregion

        #region Variables

        protected static readonly ILog Log = LogManager.GetLogger(typeof(MainWindow));
        //private List<TeachingMethod> _tmList;

        #endregion

        #region Methods

        public ObservableCollection<TeachingMethod> AdvanceSearch(TMSearchCondition searchCondition)
        {
            var tmList = new ObservableCollection<TeachingMethod>();

            #region SQLCE
            try
            {
                var tmListTmp = new ObservableCollection<TeachingMethod>();
                var tmDataAdapter = new tblTeachingMethodsTableAdapter();
                var tmDataset = new TLH_DB_DataSet.tblTeachingMethodsDataTable();
                // class and keyword only
                var keyword = "%" + (searchCondition.Keyword?? "") + "%";
                tmDataset = tmDataAdapter.SearchByClass(searchCondition.SelectedClassLevel,
                                                             keyword.ToLower() );
                foreach (var result in tmDataset)
                {
                    tmList.Add(new TeachingMethod
                                   {
                                       Content = result.Content,
                                       ID = result.ID,
                                       Name = result.Name,
                                       Note = result.Note,
                                       PreferredClassLevel = result.Class,
                                       PreferredSubject = new List<string>()
                                   }
                        );
                }

                // class + subject + keyword
                if (!string.IsNullOrEmpty(searchCondition.PreferredSubject))
                {
                    var tmSearchBySubject =
                            (new tblTeachingMethodPreferSubjectsTableAdapter().
                                    SearchBySubject(
                                            searchCondition.PreferredSubject));
                    foreach (var tm in tmDataset)
                    {
                        foreach (var item in tmSearchBySubject)
                        {
                            if (tm.ID == item.TeachingMethodID)
                            {
                                tmListTmp.Add(new TeachingMethod
                                                  {
                                                      Content = tm.Content,
                                                      ID = tm.ID,
                                                      Name = tm.Name,
                                                      Note = tm.Note,
                                                      PreferredClassLevel = tm.Class,
                                                      PreferredSubject = new List<string>()
                                                  }
                                    );
                            }
                        }
                    }
                }

                //    if (!string.IsNullOrEmpty(searchCondition.PreferredSubject))
                //    {
                //            var tmSearchBySubject =
                //                (new tblTeachingMethodPreferSubjectsTableAdapter().
                //                        SearchBySubject(
                //                                searchCondition.PreferredSubject));
                //        foreach (var tm in tmSearchByClass)
                //        {
                //            foreach (var item in tmSearchBySubject)
                //            {
                //                if (tm.ID == item.TeachingMethodID)
                //                {
                //                    tmListTmp.Add(new TeachingMethod
                //                                      {
                //                                          Content = tm.Content,
                //                                          ID = tm.ID,
                //                                          Name = tm.Name,
                //                                          Note = tm.Note,
                //                                          PreferredClassLevel = tm.Class,
                //                                          PreferredSubject = new List<string>()
                //                                      }
                //                        );
                //                }
                //            }
                //        }
                //    }
                //}

                //if (!string.IsNullOrEmpty(searchCondition.Keyword))
                //{
                //    foreach (var result in tmListTmp)
                //    {
                //        if (result.Content.ToLower().Contains(searchCondition.Keyword.ToLower()))
                //        {
                //            tmList.Add(result);
                //        }
                //    }
                //} 
                //else
                //{
                //    tmList = tmListTmp;
                //}

                var tmSubDataset = new tblTeachingMethodPreferSubjectsTableAdapter();
                foreach (var tm in tmList)
                {
                    var subjectList = tmSubDataset.GetSubjectByTMID(tm.ID);
                    foreach (var item in subjectList)
                    {
                        tm.PreferredSubject.Add(item.Subject);
                    }
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][AdvanceSearch]Error:" + e.Message);
            }

            #endregion)

            return tmList;
        }

        /// <summary>
        /// Search TeachingMethos Data in a XML file.
        /// </summary>
        /// <param name="searchCondition">Specify the search condition</param>
        /// <returns>ObservableCollection of IData</returns>
        public ObservableCollection<TeachingMethod> Search(TMSearchCondition searchCondition)
        {
            var tmList = new ObservableCollection<TeachingMethod>();
            try
            {
                if (string.IsNullOrEmpty(searchCondition.Keyword)) return GetAll();
                var keyword = searchCondition.Keyword.ToLower();
                var tmDataset = (new tblTeachingMethodsTableAdapter()).Search(keyword);
                foreach (var result in
                    Enumerable.Where(tmDataset, result => result.Content.ToLower().Contains(keyword)))
                {
                    tmList.Add(new TeachingMethod
                                   {
                                       Content = result.Content,
                                       ID = result.ID,
                                       Name = result.Name,
                                       Note = result.Note,
                                       PreferredClassLevel = result.Class
                                   }
                        );
                }

                var tmSubDataset = new tblTeachingMethodPreferSubjectsTableAdapter();
                foreach (var tm in tmList)
                {
                    var subjectList = tmSubDataset.GetSubjectByTMID(tm.ID);
                    foreach (var item in subjectList)
                    {
                        tm.PreferredSubject.Add(item.Subject);
                    }
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][Search]Error:" + e.Message);
            }
            return tmList;
        }

        /// <summary>
        /// Get all TMs
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<TeachingMethod> GetAll()
        {
            var tmList = new ObservableCollection<TeachingMethod>();

            #region SQLCE
            try
            {
                var tmDataset = (new tblTeachingMethodsTableAdapter()).GetData();
                foreach (var result in tmDataset)
                {
                    tmList.Add(new TeachingMethod
                                   {
                                       Content = result.Content,
                                       ID = result.ID,
                                       Name = result.Name,
                                       Note = result.Note,
                                       PreferredClassLevel = result.Class
                                   }
                        );
                }
                var tmSubDataset = new tblTeachingMethodPreferSubjectsTableAdapter();
                foreach (var tm in tmList)
                {
                    var subjectList = tmSubDataset.GetSubjectByTMID(tm.ID);
                    foreach (var item in subjectList)
                    {
                        tm.PreferredSubject.Add(item.Subject);
                    }
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][GetAll]Error:" + e.Message);
            }

            #endregion

            return tmList;
        }

        /// <summary>
        /// Get list of existed subject
        /// </summary>
        public ObservableCollection<string> GetSubjectList()
        {
            var subjectList = new ObservableCollection<string>();

            #region SQLCE
            try
            {
                var subjectDataSet = (new tblSubjectsTableAdapter()).GetSubjectName();
                foreach (var subject in subjectDataSet)
                {
                    subjectList.Add(subject.Name);
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][GetSubjectList]Error:" + e.Message);
            }

            #endregion

            return subjectList;

        }

        /// <summary> 
        /// help convert IEnumerable to List of string
        /// </summary>
        public List<string> ParseIEnumerable(IEnumerable<XElement> iEnumerable)
        {
            var liststr = new List<string>();

            try
            {
                var arrSubject = iEnumerable.ToArray();
                foreach (var element in arrSubject)
                {
                    var strSubject = element.ToString().Replace("<PreferredSubject>", "").Replace(
                        "</PreferredSubject>", "");
                    if (!String.IsNullOrEmpty(strSubject))
                    {
                        liststr.Add(strSubject);
                    }
                }

            }
            catch (Exception ex)
            {
                Log.Debug(ex.Message);
            }

            return liststr;
        }

        /// <summary>
        /// Add new TM to the database
        /// </summary>
        /// <param name="teachingMethod">Specify the Teaching Method Object</param>
        /// <returns>Boolean</returns>
        public int Add(TeachingMethod teachingMethod)
        {

            #region SQLCE
            if (teachingMethod == null) return -1;

            int result = -1;
            try
            {
                result = (new tblTeachingMethodsTableAdapter()).
                             Insert1(teachingMethod.Name,
                                     teachingMethod.PreferredClassLevel,
                                     teachingMethod.Content, teachingMethod.Note);
                result = Int32.Parse(new tblTeachingMethodsTableAdapter().GetNewestTMID().ToString());

                foreach (var subject in teachingMethod.PreferredSubject)
                {
                    (new tblTeachingMethodPreferSubjectsTableAdapter()).Insert1(result, subject);
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][Add]Error:" + e.Message);
                return result;
            }

            #endregion)

            return result;

        }

        /// <summary>
        /// Update TeachingMethod Data in a XML file.
        /// </summary>
        /// <param name="teachingMethod">Specify the Teaching Method Object</param>
        /// <returns>Boolean</returns>
        public bool Update(TeachingMethod teachingMethod)
        {

            #region SQLCE
            if (teachingMethod == null) return false;

            bool result = false;
            try
            {
                result = (new tblTeachingMethodsTableAdapter()).
                             Update1(teachingMethod.Name,
                                     teachingMethod.PreferredClassLevel,
                                     teachingMethod.Content, teachingMethod.Note,
                                     teachingMethod.ID) > 0;
                (new tblTeachingMethodPreferSubjectsTableAdapter()).DeleteTMSubject(teachingMethod.ID);
                foreach (var subject in teachingMethod.PreferredSubject)
                {
                    result = (new tblTeachingMethodPreferSubjectsTableAdapter()).Insert1(teachingMethod.ID, subject) > 0;
                }
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][Update]Error:" + e.Message);
            }

            #endregion)

            return result;

        }

        public bool Delete(TeachingMethod teachingMethod)
        {

            #region SQLCE

            var result = false;
            try
            {
                if (teachingMethod == null) return false;
                result = (new tblTeachingMethodsTableAdapter()).
                             Delete(teachingMethod.ID) > 0;
            }
            catch (Exception e)
            {
                Log.Debug("[TMAccessor][Delete]Error:" + e.Message);
            }

            #endregion)

            return result;

        }

        #endregion

    }
}
